from clickhouse_driver import Client
import pymysql
from crawlab import save_item


conn = pymysql.connect(host="47.109.83.232", port=13306, user="root", password="tidb@guest.123", db="appraise_jsf",charset="utf8")

soure_tables = []
count = 0

try:
    cursor = conn.cursor()
    with conn.cursor() as cursor:
        cursor.execute("show tables")
        for s in cursor.fetchall():
            soure_tables.append(s[0])
except pymysql.MySQLError as error:
    print(error)
finally:
    cursor.close()
    conn.close()


client = Client(host='172.16.10.187', port='9000', user='default', password='root.guest.123')
soure_table = soure_tables.copy()
di = {'django_session': 'session_key', 'third_verification': 'unionid','source_info':'adcode','authtoken_token':'key','django_apscheduler_djangojob':'ID'}
for i in soure_table:
    if i.find('v_') != -1:
        soure_tables.remove(i)
        continue
    ord = 'id'
    if i in di.keys():
        ord = di.get(i)
    sql = """create table appraise_jsf.{table}  ENGINE=MergeTree order by {order} as select * from mysql('47.109.83.232:13306', 'appraise_jsf' , {table} , 'root', 'tidb@guest.123')""".format(table=i,order=ord)
    try:
        # client.execute("drop table appraise_jsf.{}".format(i))
        ans = client.execute(sql)
        print(ans)
        count += 1
    except Exception as e:
        print(i)
        print(e)
print(soure_tables)
print('tidb共有'+str(len(soure_tables))+"个表")
print('共有'+str(count)+"个表复制成功")
result = {'mysql共有数据表':len(soure_tables),'复制完成数据表': count }
save_item(result)

